﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace SysMisc
{
    public class SqlAccess
    {
        //与SQL Server的连接字符串设置
        private string _connString;
        private string _strSql;

        private SqlCommandBuilder sqlCmdBuilder;
        private DataSet ds = new DataSet();
        private SqlDataAdapter da;
        public SqlAccess(string connString)
        {
            this._connString = connString;
        }
        public SqlAccess(string connString, string strSql)
        {
            this._connString = connString;
        }

        public DataTable GetSchema( string tbName )
        {
            SqlConnection sc = this.GetConn();
            DataTable dt = sc.GetSchema(tbName);
            sc.Close();
            return dt;

        }

        //根据输入的SQL语句检索数据库数据
        public DataSet SelectDb(string strSql, string strTableName)
        {
            try
            {
                SqlConnection sc = this.GetConn();
                this._strSql = strSql;
                this.da = new SqlDataAdapter(this._strSql, sc);
                this.ds.Clear();
                this.da.Fill(ds, strTableName);
                sc.Close();
                return ds;//返回填充了数据的DataSet，其中数据表以strTableName给出的字符串命名
            }
            catch (Exception ex)
            {
                LogHelper.Error(typeof(SqlAccess), ex);
                throw ex;
            }
        }

        //数据库数据更新(传DataSet和DataTable的对象)
        public DataSet UpdateDs(DataSet changedDs, string tableName)
        {
            try
            {
                SqlConnection sc = this.GetConn();
                this.da = new SqlDataAdapter(this._strSql, sc);
                this.sqlCmdBuilder = new SqlCommandBuilder(da);
                this.da.Update(changedDs, tableName);
                changedDs.AcceptChanges();
                sc.Close();
                return changedDs;//返回更新了的数据库表
            }
            catch (Exception ex)
            {
                LogHelper.Error(typeof(SqlAccess), ex);
                throw ex;
            }
        }

        /*私有方法*/
        /// <summary>
        /// 此方法，在返回对象时，就已经打开数据库
        /// </summary>
        /// <returns></returns>
        private SqlConnection GetConn()
        {
            try
            {
                SqlConnection Connection = new SqlConnection(this._connString);
                Connection.Open();
                return Connection;
            }
            catch (Exception ex)
            {
                LogHelper.Error(typeof(SqlAccess), ex);
                throw ex;
            }
        }

    }
}
